package com.gjzt.business.media.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.gjzt.business.contract.inter.ContractResourceMapper;
import com.gjzt.business.contract.model.ContractResource;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.model.BusinessException;
import com.eastdigit.system.model.SqlBuilder;
import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.ExcelReader;
import com.eastdigit.util.HttpUtil;
import com.eastdigit.util.PropertiesUtil;
import com.eastdigit.util.StringUtil;
import com.gjzt.business.media.inter.MediaBusMapper;
import com.gjzt.business.media.inter.MediaResourceMapper;
import com.gjzt.business.media.inter.MediaStopMapper;
import com.gjzt.business.media.inter.MediaTypeConfigMapper;
import com.gjzt.business.media.model.MediaBus;
import com.gjzt.business.media.model.MediaResource;
import com.gjzt.business.media.model.MediaStop;
import com.gjzt.business.media.model.MediaTypeConfig;
import com.gjzt.business.utils.Constant;

@Service
public class MediaResourceService extends BaseService {

    @Autowired
    private MediaResourceMapper mediaResourceMapper;

    @Autowired
    private MediaTypeConfigMapper mediaTypeConfigMapper;

    @Autowired
    private MediaBusMapper mediaBusMapper;

    @Autowired
    private MediaStopMapper mediaStopMapper;

    @Autowired
    private ContractResourceMapper contractResourceMapper;

    public static String ATTACHMENT_PATH = PropertiesUtil.getProperty("attachment.path");

    public static String TOKEN_CORPID = PropertiesUtil.getProperty("token.corpid");

    public static String TOKEN_SECRET = PropertiesUtil.getProperty("token.secret");

    public static String TOKEN_ASSESSURL = PropertiesUtil.getProperty("token.assessUrl");

    public static String TOKEN_BUSURL = PropertiesUtil.getProperty("token.busUrl");

    public static String DRIVER_NAME = PropertiesUtil.getProperty("sqlServer.driverName");

    public static String DB_URL = PropertiesUtil.getProperty("sqlServer.dbURL");

    public static String USERNAME = PropertiesUtil.getProperty("sqlServer.userName");

    public static String PASSWORD = PropertiesUtil.getProperty("sqlServer.password");

    public JSONObject queryMediaResourcesOfBus(ReqBean reqBean) {
        if (StringUtil.isEmpty(reqBean.getInfo("mediaType"))) {
            return null;
        }

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT");
        sql.append("	R.*, B.VEHICLE_NO,");
        sql.append("	B.PLATE_NO,");
        sql.append("	B.LINE_NO,");
        sql.append("	B.COMPANY_NAME,");
        sql.append("	B.BUS_TYPE,");
        sql.append("	B.BUS_FLOOR,");
        sql.append("	B.ONLINE_DATE,");
        sql.append("	B.VEHICLE_NO_ABBR,");
        sql.append("	B.AIR_CONDITIONED,");
        sql.append("	B.BUS_ENERGY,");
        sql.append("	B.BUSINESS_TYPE,");
        sql.append("	B.STATUS busStatus,");
        sql.append("    _C.CONTRACT_NO,");
        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))) {
            sql.append("	_P.CONTENT,");
            sql.append("	_P.PUBLISH_TIME,");
            sql.append("	_P.PUBLISH_OFF_TIME,");
        }
        sql.append("	_C.CUSTOMER_ID");
        sql.append(" FROM");
        sql.append("	MEDIA_RESOURCE R");
        sql.append(" INNER JOIN MEDIA_BUS B ON R.BUS_ID = B.BUS_ID");
        sql.append(" LEFT JOIN (");
        sql.append("	SELECT");
        sql.append("		C.CONTRACT_NO,");
        sql.append("		C.CUSTOMER_ID,");
        sql.append("		CR.RESOURCE_ID");
        sql.append("	FROM");
        sql.append("		AD_CONTRACT C,");
        sql.append("		AD_CONTRACT_RESOURCE CR");
        sql.append("	WHERE");
        sql.append("		C.CONTRACT_ID = CR.CONTRACT_ID");
        sql.append("		AND NOW() BETWEEN CR.START_DATE and CR.END_DATE");
        sql.append(") _C ON _C.RESOURCE_ID = R.RESOURCE_ID");
        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))) {
            sql.append(" LEFT JOIN (");
            sql.append("	SELECT");
            sql.append("		P.BILL_NO,");
            sql.append("		P.CONTENT,");
            sql.append("		P.PUBLISH_TIME,");
            sql.append("		P.PUBLISH_OFF_TIME,");
            sql.append("		PD.RESOURCE_ID");
            sql.append("	FROM");
            sql.append("		AD_PUBLISH_BILL P,");
            sql.append("		AD_PUBLISH_BILL_DETAIL PD");
            sql.append("	WHERE");
            sql.append("		P.BILL_ID = PD.BILL_ID");
            sql.append("		AND NOW() BETWEEN P.PUBLISH_TIME and P.PUBLISH_OFF_TIME");
            sql.append(") _P ON _P.RESOURCE_ID = R.RESOURCE_ID");
        }
        sql.append(" WHERE");
        sql.append("	R.MEDIA_TYPE = '" + reqBean.getInfo("mediaType") + "'");
        if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNo"))) {
            sql.append(" and B.VEHICLE_NO = '").append(reqBean.getInfo("vehicleNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNoAbbr"))) {
            sql.append(" and B.VEHICLE_NO_ABBR = '").append(reqBean.getInfo("vehicleNoAbbr")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("plateNo"))) {
            sql.append(" and B.PLATE_NO = '").append(reqBean.getInfo("plateNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("lineNo"))) {
            sql.append(" and B.LINE_NO = '").append(reqBean.getInfo("lineNo")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("companyId"))) {
            sql.append(" and B.COMPANY_ID = '").append(reqBean.getInfo("companyId")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busType"))) {
            sql.append(" and B.BUS_TYPE = '").append(reqBean.getInfo("busType")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busFloor"))) {
            sql.append(" and B.BUS_FLOOR = '").append(reqBean.getInfo("busFloor")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("airConditioned"))) {
            sql.append(" and B.AIR_CONDITIONED = '").append(reqBean.getInfo("airConditioned")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("busEnergy"))) {
            sql.append(" and B.BUS_ENERGY = '").append(reqBean.getInfo("busEnergy")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("businessType"))) {
            sql.append(" and B.BUSINESS_TYPE = '").append(reqBean.getInfo("businessType")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("status"))) {
            sql.append(" and B.STATUS = '").append(reqBean.getInfo("status")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("contractNo"))) {
            sql.append(" and _C.CONTRACT_NO = '").append(reqBean.getInfo("contractNo")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("customerId"))) {
            sql.append(" 	AND _C.CUSTOMER_ID = '").append(reqBean.getInfo("customerId")).append("'");
        }

        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))
                && !StringUtil.isEmpty(reqBean.getInfo("content"))) {
            sql.append(" and _P.CONTENT like '%").append(reqBean.getInfo("content")).append("%'");
        }

        sql.append(" ORDER BY R.BUS_ID ");

        // StringBuffer sql = new StringBuffer("select r.* ");
        // sql.append(" ,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,b.COMPANY_NAME,b.BUS_TYPE,b.BUS_FLOOR,b.ONLINE_DATE,b.VEHICLE_NO_ABBR,b.AIR_CONDITIONED,b.BUS_ENERGY,b.BUSINESS_TYPE ");
        // if
        // (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType")))
        // {
        // sql.append(" ,c.CONTRACT_NO,c.CUSTOMER_ID,cu.CUSTOMER_NAME,t.resourceStart2EndTime,t.CONTENT ");
        // } else {
        // sql.append(" ,c.CONTRACT_NO,c.CUSTOMER_ID,cu.CUSTOMER_NAME ");
        // }
        //
        // sql.append(" from MEDIA_RESOURCE r ");
        // sql.append(" left join MEDIA_BUS b on r.BUS_ID = b.BUS_ID ");
        // sql.append(" left join AD_CONTRACT_RESOURCE cr ON cr.RESOURCE_ID = r.RESOURCE_ID AND NOW() BETWEEN cr.START_DATE and cr.END_DATE ");
        // sql.append(" left join AD_CONTRACT c on cr.CONTRACT_ID = c.CONTRACT_ID ");
        // sql.append(" left join AD_CUSTOMER cu on c.CUSTOMER_ID = cu.CUSTOMER_ID ");
        //
        // if
        // (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType")))
        // {
        // sql.append(" LEFT JOIN (SELECT CONCAT(pb.PUBLISH_TIME,'至',pb.PUBLISH_OFF_TIME) resourceStart2EndTime,pb.CONTENT,pbd.RESOURCE_ID FROM AD_PUBLISH_BILL_DETAIL pbd");
        // sql.append(" LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = pbd.BILL_ID");
        // sql.append(" AND NOW() BETWEEN pb.PUBLISH_TIME AND pb.PUBLISH_OFF_TIME ORDER BY pb.PUBLISH_OFF_TIME desc) t ON t.RESOURCE_ID = r.RESOURCE_ID");
        // }
        // sql.append(" where 1 = 1 ");
        //
        // if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNo"))) {
        // sql.append(" and b.VEHICLE_NO = '").append(reqBean.getInfo("vehicleNo")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("vehicleNoAbbr"))) {
        // sql.append(" and b.VEHICLE_NO_ABBR = '").append(reqBean.getInfo("vehicleNoAbbr")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("plateNo"))) {
        // sql.append(" and b.PLATE_NO = '").append(reqBean.getInfo("plateNo")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("lineNo"))) {
        // sql.append(" and b.LINE_NO = '").append(reqBean.getInfo("lineNo")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("companyId"))) {
        // sql.append(" and b.COMPANY_ID = '").append(reqBean.getInfo("companyId")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("busType"))) {
        // sql.append(" and b.BUS_TYPE = '").append(reqBean.getInfo("busType")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("busFloor"))) {
        // sql.append(" and b.BUS_FLOOR = '").append(reqBean.getInfo("busFloor")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("airConditioned"))) {
        // sql.append(" and b.AIR_CONDITIONED = '").append(reqBean.getInfo("airConditioned")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("busEnergy"))) {
        // sql.append(" and b.BUS_ENERGY = '").append(reqBean.getInfo("busEnergy")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("businessType"))) {
        // sql.append(" and b.BUSINESS_TYPE = '").append(reqBean.getInfo("businessType")).append("'");
        // }
        // if (!StringUtil.isEmpty(reqBean.getInfo("status"))) {
        // sql.append(" and b.STATUS = '").append(reqBean.getInfo("status")).append("'");
        // }
        //
        // if (!StringUtil.isEmpty(reqBean.getInfo("mediaType"))) {
        // sql.append(" and r.MEDIA_TYPE = '").append(reqBean.getInfo("mediaType")).append("'");
        // }
        //
        // if (!StringUtil.isEmpty(reqBean.getInfo("content"))) {
        // sql.append(" and t.CONTENT like '%").append(reqBean.getInfo("content")).append("%'");
        // }
        //
        // if (!StringUtil.isEmpty(reqBean.getInfo("customerId"))) {
        // sql.append(" and c.CUSTOMER_ID = '").append(reqBean.getInfo("customerId")).append("'");
        // }
        //
        // //
        // sql.append(" order by b.COMPANY_NAME, b.LINE_NO, b.PLATE_NO , r.RESOURCE_CODE ");
        //
        // if
        // (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType")))
        // {
        // sql.append(" GROUP BY r.RESOURCE_ID");
        // }

        return this.queryTable(sql.toString(), null, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    public JSONObject queryMediaResourcesOfStop(ReqBean reqBean) {
        StringBuffer sql = new StringBuffer("select r.* ");
        sql.append(" ,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,s.COUNTY STOP_COUNTY,s.STATUS STOP_STATUS ,s.LONGITUDE,s.LATITUDE");

        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))) {
            sql.append(" ,c.CONTRACT_NO,c.CUSTOMER_ID,cu.CUSTOMER_NAME,t.PUBLISH_TIME,t.PUBLISH_OFF_TIME, t.CONTENT ");
        } else {
            sql.append(" ,c.CONTRACT_NO,c.CUSTOMER_ID,cu.CUSTOMER_NAME ");
        }

        // sql.append(" ,c.CONTRACT_NO,c.CUSTOMER_ID,cu.CUSTOMER_NAME,cr.START_DATE,cr.END_DATE ");
        sql.append(" from MEDIA_RESOURCE r ");
        sql.append(" left join MEDIA_STOP s on r.STOP_ID = s.STOP_ID ");
        sql.append(" left join AD_CONTRACT_RESOURCE cr ON cr.RESOURCE_ID = r.RESOURCE_ID AND NOW() BETWEEN cr.START_DATE and cr.END_DATE ");
        sql.append(" left join AD_CONTRACT c on cr.CONTRACT_ID = c.CONTRACT_ID ");
        sql.append(" left join AD_CUSTOMER cu on c.CUSTOMER_ID = cu.CUSTOMER_ID ");

        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))) {
            sql.append(" LEFT JOIN (SELECT pb.PUBLISH_TIME,pb.PUBLISH_OFF_TIME,pb.CONTENT,pbd.RESOURCE_ID FROM AD_PUBLISH_BILL_DETAIL pbd");
            sql.append(" LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = pbd.BILL_ID");
            sql.append(" AND NOW() BETWEEN pb.PUBLISH_TIME AND pb.PUBLISH_OFF_TIME ORDER BY pb.PUBLISH_OFF_TIME desc) t ON t.RESOURCE_ID = r.RESOURCE_ID");
        }

        sql.append(" where 1 = 1 ");
        if (!StringUtil.isEmpty(reqBean.getInfo("stopName"))) {
            sql.append(" and s.STOP_NAME = '").append(reqBean.getInfo("stopName")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("roadName"))) {
            sql.append(" and s.ROAD_NAME = '").append(reqBean.getInfo("roadName")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("direction"))) {
            sql.append(" and s.DIRECTION = '").append(reqBean.getInfo("direction")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("province"))) {
            sql.append(" and s.PROVINCE = '").append(reqBean.getInfo("province")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("city"))) {
            sql.append(" and s.CITY = '").append(reqBean.getInfo("city")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("county"))) {
            sql.append(" and s.COUNTY = '").append(reqBean.getInfo("county")).append("'");
        }
        if (!StringUtil.isEmpty(reqBean.getInfo("status"))) {
            sql.append(" and s.STATUS = '").append(reqBean.getInfo("status")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("mediaType"))) {
            sql.append(" and r.MEDIA_TYPE = '").append(reqBean.getInfo("mediaType")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("contractNo"))) {
            sql.append(" and c.CONTRACT_NO = '").append(reqBean.getInfo("contractNo")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("customerId"))) {
            sql.append(" and c.CUSTOMER_ID = '").append(reqBean.getInfo("customerId")).append("'");
        }

        if (!StringUtil.isEmpty(reqBean.getInfo("content"))) {
            sql.append(" and t.CONTENT like '%").append(reqBean.getInfo("content")).append("%'");
        }

        if (Constant.MEDIA_MODEL_TYPE_SINGLE.equals(reqBean.getInfo("modelType"))) {
            sql.append(" GROUP BY r.RESOURCE_ID");
        }

        return this.queryTable(sql.toString(), null, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    /**
     * 根据busId查询车辆资源点位
     * 
     * @param busId
     * @return
     */
    public JSONArray findBusMediaResources(String busId) {
        StringBuffer sql = new StringBuffer("select r.*, mt.MEDIA_NAME from MEDIA_RESOURCE r ");
        sql.append(" left join MEDIA_TYPE mt on mt.MEDIA_ID = r.MEDIA_TYPE where r.BUS_ID = '").append(busId)
                .append("'");
        sql.append(" order by r.MEDIA_TYPE , RESOURCE_CODE ");
        return this.query(sql.toString());
    }

    /**
     * 根据stopId查询站点资源点位
     * 
     * @param stopId
     * @return
     */
    public JSONArray findStopMediaResources(String stopId) {

        // String equipmentSql =
        // "SELECT e.EQUIPMENT_ID,e.EQUIPMENT_TYPE FROM MEDIA_EQUIPMENT e where e.STOP_ID = '"
        // + stopId + "'";
        // JSONArray equipmentList = query(equipmentSql);
        // if (equipmentList != null && equipmentList.size() > 0) {
        // for (int i = 0; i < equipmentList.size(); i++) {
        // JSONObject json = equipmentList.getJSONObject(i);
        // String equipmentId = json.getString("equipmentId");
        // String sql = "SELECT r.*,t.MEDIA_NAME FROM MEDIA_RESOURCE r "
        // + "LEFT JOIN MEDIA_TYPE t ON t.MEDIA_ID = r.MEDIA_TYPE " +
        // "where r.STOP_ID = '" + stopId
        // + "' AND r.EQUIPMENT_ID = '" + equipmentId + "'";
        // JSONArray resourceList = query(sql);
        // json.put("resourceList", resourceList);
        // }
        // }
        // return equipmentList;

        StringBuffer sql = new StringBuffer("select r.*, mt.MEDIA_NAME from MEDIA_RESOURCE r ");
        sql.append(" left join MEDIA_TYPE mt on mt.MEDIA_ID = r.MEDIA_TYPE where r.STOP_ID = '").append(stopId)
                .append("'");
        sql.append(" order by r.MEDIA_TYPE , RESOURCE_CODE ");
        return this.query(sql.toString());
    }

    /**
     * 新增
     * 
     * @param reqBean
     */
    public void addMediaResource(ReqBean reqBean) {
        MediaResource mediaResource = getMediaResource(reqBean);
        if (StringUtil.isEmpty(mediaResource.getResourceCode())) {
            throw new BusinessException("媒体资源编号不能为空!");
        }
        if (mediaResourceMapper.selectByResourceCode(mediaResource.getResourceCode()) != null) {
            throw new BusinessException("媒体资源编号已占用!");
        }

        mediaResource.setResourceId(stringId());
        mediaResource.setStatus(Constant.DATA_STATUS_TRUE);
        mediaResource.setOperator(reqBean.getUserId());
        mediaResource.setMakeTime(new Date());
        mediaResourceMapper.insert(mediaResource);
    }

    /**
     * 修改
     * 
     * @param reqBean
     */
    public void updateMediaResource(ReqBean reqBean) {
        String resourceId = reqBean.getInfo("resourceId");
        if (StringUtil.isEmpty(resourceId)) {
            throw new BusinessException("获取主键失败");
        }
        MediaResource mediaResource = getMediaResource(reqBean);
        if (StringUtil.isEmpty(mediaResource.getResourceCode())) {
            throw new BusinessException("媒体资源编号不能为空!");
        }
        MediaResource dbObj = mediaResourceMapper.selectByPrimaryKey(mediaResource.getResourceId());
        if (!mediaResource.getResourceCode().equals(dbObj.getResourceCode())
                && mediaResourceMapper.selectByResourceCode(mediaResource.getResourceCode()) != null) {
            throw new BusinessException("媒体资源编号已占用!");
        }

        mediaResource.setOperator(reqBean.getUserId());
        mediaResource.setModifyTime(new Date());
        mediaResourceMapper.updateByPrimaryKeySelective(mediaResource);
    }

    public MediaResource getMediaResource(String resourceId) {
        return mediaResourceMapper.selectByPrimaryKey(resourceId);
    }

    /**
     * 失效
     * 
     * @param resourceId
     */
    public void removeMediaResource(String resourceId) {
        if (StringUtil.isEmpty(resourceId)) {
            throw new BusinessException("获取主键失败");
        }
        mediaResourceMapper.updateResourceStatus(resourceId, Constant.DATA_STATUS_FALSE);
    }

    /**
     * 恢复
     * 
     * @param resourceId
     */
    public void revertMediaResource(String resourceId) {
        if (StringUtil.isEmpty(resourceId)) {
            throw new BusinessException("获取主键失败");
        }
        mediaResourceMapper.updateResourceStatus(resourceId, Constant.DATA_STATUS_TRUE);
    }

    /**
     * 查找资源广告刊载记录
     * 
     * @param resourceId
     * @return
     */
    public JSONObject findAdsOfResource(ReqBean reqBean) {
        StringBuffer sql = new StringBuffer("select b.* ");
        sql.append(" ,c.CUSTOMER_NAME ");
        sql.append(" from AD_PUBLISH_BILL b ");
        sql.append(" INNER JOIN AD_PUBLISH_BILL_DETAIL d on d.BILL_ID = b.BILL_ID");
        sql.append(" LEFT JOIN AD_CUSTOMER c on b.CUSTOMER_ID = c.CUSTOMER_ID");
        sql.append(" where d.RESOURCE_ID = '").append(reqBean.getInfo("resourceId")).append("'");
        sql.append(" order by b.PUBLISH_TIME desc");
        return this.queryTable(sql.toString(), null, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    /**
     * 查询资源权属合同记录
     * 
     * @param resourceId
     * @return
     */
    public JSONObject findRightsOfResource(ReqBean reqBean) {
        StringBuffer sql = new StringBuffer("select c.* ");
        sql.append(" ,cr.ID,cr.START_DATE, cr.END_DATE,cr.PRICE,CONCAT(cu.CUSTOMER_NAME,'-',c.CONTRACT_NO) name ,c.CONTRACT_ID value ");
        sql.append(" ,cu.CUSTOMER_NAME ");
        sql.append(" from AD_CONTRACT c ");
        sql.append(" INNER JOIN AD_CONTRACT_RESOURCE cr on c.CONTRACT_ID = cr.CONTRACT_ID");
        sql.append(" LEFT JOIN AD_CUSTOMER cu on c.CUSTOMER_ID = cu.CUSTOMER_ID");
        sql.append(" where cr.RESOURCE_ID = '").append(reqBean.getInfo("resourceId")).append("'");
        sql.append(" order by cr.START_DATE desc");
        return this.queryTable(sql.toString(), null, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    /**
     * 从表单获取对象
     * 
     * @param reqBean
     * @return
     */
    private MediaResource getMediaResource(ReqBean reqBean) {
        MediaResource mediaResource = new MediaResource();

        mediaResource.setResourceId(reqBean.getInfo("resourceId"));
        mediaResource.setResourceCode(reqBean.getInfo("resourceCode"));
        mediaResource.setResourceName(reqBean.getInfo("resourceName"));
        mediaResource.setMediaType(reqBean.getInfo("mediaType"));
        mediaResource.setBusId(reqBean.getInfo("busId"));
        mediaResource.setStopId(reqBean.getInfo("stopId"));
        mediaResource.setEquipmentId(reqBean.getInfo("equipmentId"));
        mediaResource.setResourceLevel(reqBean.getInfo("resourceLevel"));
        mediaResource.setDayPrice(reqBean.getInfoDouble("dayPrice"));
        mediaResource.setMonthPrice(reqBean.getInfoDouble("monthPrice"));
        mediaResource.setYearPrice(reqBean.getInfoDouble("yearPrice"));
        mediaResource.setModel(reqBean.getInfo("model"));
        mediaResource.setDescription(reqBean.getInfo("description"));
        mediaResource.setProvince(reqBean.getInfo("province"));
        mediaResource.setCity(reqBean.getInfo("city"));
        mediaResource.setCounty(reqBean.getInfo("county"));
        mediaResource.setAddress(reqBean.getInfo("address"));
        mediaResource.setStatus(reqBean.getInfo("status"));
        mediaResource.setResourceImg(reqBean.getInfo("resourceImg"));
        mediaResource.setReCompanyId(reqBean.getInfo("reCompanyId"));
        mediaResource.setOperator(reqBean.getUserId());
        mediaResource.setMakeTime(DateUtil.parseDateTime(reqBean.getInfo("makeTime")));
        mediaResource.setModifyTime(new Date());
        mediaResource.setRemark(reqBean.getInfo("remark"));

        mediaResource.setItem1(reqBean.getInfo("item1"));
        mediaResource.setItem2(reqBean.getInfo("item2"));
        mediaResource.setItem3(reqBean.getInfo("item3"));
        mediaResource.setItem4(reqBean.getInfo("item4"));
        mediaResource.setItem5(reqBean.getInfo("item5"));
        mediaResource.setItem6(reqBean.getInfo("item6"));
        mediaResource.setItem7(reqBean.getInfo("item7"));
        mediaResource.setItem8(reqBean.getInfo("item8"));
        mediaResource.setItem9(reqBean.getInfo("item9"));
        mediaResource.setItem10(reqBean.getInfo("item10"));
        mediaResource.setItem11(reqBean.getInfo("item11"));
        mediaResource.setItem12(reqBean.getInfo("item12"));
        mediaResource.setItem13(reqBean.getInfo("item13"));
        mediaResource.setItem14(reqBean.getInfo("item14"));
        mediaResource.setItem15(reqBean.getInfo("item15"));
        mediaResource.setItem16(reqBean.getInfo("item16"));
        mediaResource.setItem17(reqBean.getInfo("item17"));
        mediaResource.setItem18(reqBean.getInfo("item18"));
        mediaResource.setItem19(reqBean.getInfo("item19"));
        mediaResource.setItem20(reqBean.getInfo("item20"));

        return mediaResource;
    }

    public JSONArray dynamicBusHead(ReqBean reqBean) {
        String mediaType = reqBean.getInfo("mediaType");
        if (StringUtils.isBlank(mediaType)) {
            throw new BusinessException("缺少参数!");
        }
        List<MediaTypeConfig> mediaTypeConfigs = mediaTypeConfigMapper.selectByMediaId(mediaType);
        JSONArray returnArr = new JSONArray();
        JSONObject json = new JSONObject();
        json.put("col", "plateNo");
        json.put("name", "车牌号");
        returnArr.add(json);
        json.put("col", "vehicleNo");
        json.put("name", "自编号");
        returnArr.add(json);
        json.put("col", "lineNo");
        json.put("name", "线路号");
        returnArr.add(json);
        json.put("col", "companyName");
        json.put("name", "营运公司");
        returnArr.add(json);

        if (mediaTypeConfigs != null && mediaTypeConfigs.size() > 0) {
            for (int i = 0; i < mediaTypeConfigs.size(); i++) {
                MediaTypeConfig mediaTypeConfig = mediaTypeConfigs.get(i);
                json.put("col", mediaTypeConfig.getItemId());
                json.put("name", mediaTypeConfig.getItemName());
                json.put("type", mediaTypeConfig.getItemType());
                returnArr.add(json);
            }
        }

        json.put("col", "");
        json.put("name", "操作");
        json.put("width", "200px");
        returnArr.add(json);
        return returnArr;
    }

    public JSONObject dynamicBusData(ReqBean reqBean) {
        String mediaType = reqBean.getInfo("mediaType");
        if (StringUtils.isBlank(mediaType)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "SELECT a.*,mb.PLATE_NO,mb.COMPANY_ID,mb.COMPANY_NAME,mb.VEHICLE_NO,mb.LINE_NO FROM `MEDIA_RESOURCE` a "
                + "LEFT JOIN MEDIA_BUS mb ON mb.BUS_ID = a.BUS_ID where a.MEDIA_TYPE = '" + mediaType + "'";
        SqlBuilder sqlBuilder = new SqlBuilder(sql).addWhere("a.RESOURCE_ID", reqBean.getInfo("resourceId"));
        return queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    public JSONObject dynamicHeadLength(ReqBean reqBean) {
        String mediaType = reqBean.getInfo("mediaType");
        if (StringUtils.isBlank(mediaType)) {
            throw new BusinessException("缺少参数!");
        }
        List<MediaTypeConfig> mediaTypeConfigs = mediaTypeConfigMapper.selectByMediaId(mediaType);
        JSONObject json = new JSONObject();
        json.put("headLength", mediaTypeConfigs.size());
        return json;
    }

    public JSONObject dynamicResourceDetailsHead(ReqBean reqBean) {

        String mediaType = reqBean.getInfo("mediaType");
        String resourceId = reqBean.getInfo("resourceId");
        if (StringUtils.isBlank(mediaType) || StringUtils.isBlank(resourceId)) {
            throw new BusinessException("缺少参数!");
        }
        JSONObject returnJson = new JSONObject();

        String sql = "SELECT a.* FROM `MEDIA_RESOURCE` a " + " where a.MEDIA_TYPE = '" + mediaType
                + "' and a.RESOURCE_ID = '" + resourceId + "'";
        // if(Constant.MEDIA_TYPE_BUS.equals(parentType)) {
        // sql = "SELECT a.* FROM `MEDIA_RESOURCE` a "
        // + " where a.MEDIA_TYPE = '" + mediaType + "' and a.RESOURCE_ID = '" +
        // resourceId + "'";
        // } else if(Constant.MEDIA_TYPE_STOP.equals(parentType)){
        //
        // }

        JSONArray detailDatas = query(sql);
        returnJson.put("detailDatas", detailDatas);

        List<MediaTypeConfig> mediaTypeConfigs = mediaTypeConfigMapper.selectByMediaId(mediaType);
        JSONArray returnArr = new JSONArray();
        JSONObject json = new JSONObject();
        // json.put("col", "resourceLevel");
        // json.put("name", "级别");
        // returnArr.add(json);

        json.put("col", "model");
        json.put("name", "规格");
        returnArr.add(json);

        if (mediaTypeConfigs != null && mediaTypeConfigs.size() > 0) {
            for (int i = 0; i < mediaTypeConfigs.size(); i++) {
                MediaTypeConfig mediaTypeConfig = mediaTypeConfigs.get(i);
                String itemType = mediaTypeConfig.getItemType();
                String enumCode = mediaTypeConfig.getEnumCode();
                json.put("name", mediaTypeConfig.getItemName());
                json.put("col", mediaTypeConfig.getItemId());
                json.put("type", itemType);
                json.put("code", enumCode);
                returnArr.add(json);
            }
        }

        json.put("col", "resourceImg");
        json.put("name", "照片");
        json.put("type", "showImg");
        returnArr.add(json);

        returnJson.put("headDatas", returnArr);

        return returnJson;
    }

    public void importData(ReqBean reqBean) {
        String path = reqBean.getInfo("path");
        if (StringUtils.isBlank(path)) {
            throw new BusinessException("缺少参数!");
        }
        String filePath = ATTACHMENT_PATH + "file/" + path.substring(path.indexOf("/") + 1);
        ExcelReader excel = new ExcelReader(filePath);

        int row = excel.getRowNum(0);

        if (excel == null || row == 0) {
            throw new BusinessException("导入的数据为空!");
        }
        List<String[]> dataList = excel.getAllData(0);
        int hctCount = 0;
        for (int i = 1; i <= row; i++) {
            if (i >= dataList.size()) {
                break;
            }
            // String stopName = dataList.get(i)[0];
            // String direction = dataList.get(i)[1];
            // String remark = dataList.get(i)[2];
            // String deputyStopName = dataList.get(i)[3];
            // String roadName = dataList.get(i)[4];
            // String passlineCount = dataList.get(i)[7];
            // String passLines = dataList.get(i)[8];
            // String lineBoardMaterial = dataList.get(i)[9];
            // String lineBoardSize = dataList.get(i)[10];
            // String lineBoardCreator = dataList.get(i)[11];
            //
            // String zgStyle = dataList.get(i)[12];
            // String bigBoardCount = dataList.get(i)[15];
            // String smallBoardCount = dataList.get(i)[16];
            // String maintenanceCompany = dataList.get(i)[17];
            //
            // MediaStop stop = new MediaStop();
            // stop.setStopId(UUID.randomUUID() + "");
            // stop.setStopName(stopName);
            // stop.setDirection(direction);
            // stop.setRemark(remark);
            // stop.setDeputyStopName(deputyStopName);
            // stop.setRoadName(roadName);
            // stop.setPassLines(passLines);
            // stop.setPassLineCount(passlineCount);
            // stop.setLineBoardCreator(lineBoardCreator);
            // stop.setLineBoardMaterial(lineBoardMaterial);
            // stop.setLineBoardSize(lineBoardSize);
            // stop.setZgStyle(zgStyle);
            // stop.setMaintenanceCompany(maintenanceCompany);
            // stop.setStatus("1");
            // stop.setMakeTime(new Date());
            // stop.setModifyTime(new Date());

            String stopNo = dataList.get(i)[0];
            String county = dataList.get(i)[1];
            String stopName = dataList.get(i)[2];
            String direction = dataList.get(i)[3];
            String ledNum = dataList.get(i)[4].trim();
            String zpjdSituation = dataList.get(i)[5];
            String remark = dataList.get(i)[6];
            String deputyStopName = dataList.get(i)[7];
            String customerName = dataList.get(i)[8].trim();
            String bigBoardCount = dataList.get(i)[9].trim();
            String smallBoardCount = dataList.get(i)[10].trim();
            String ldSituation = dataList.get(i)[11];
            String hctss = dataList.get(i)[12];
            String roadName = dataList.get(i)[13];

            String passLineCount = dataList.get(i)[15];
            String passLines = dataList.get(i)[16];
            String lineBoardCreator = dataList.get(i)[17];
            String lineBoardMaterial = dataList.get(i)[18];
            String lineBoardSize = dataList.get(i)[19];
            String zgStyle = dataList.get(i)[20];

            // System.out.println(i + "-" + stopName + "-" + remark + "-" +
            // direction + "-" + bigBoardCount + '-' + smallBoardCount);
            // String ldSituation = dataList.get(i)[11];
             MediaStop stop = null;
//            -------------------------------------------------------------------------
//            if (StringUtils.isNotBlank(stopNo)) {
//                System.out.println(stopNo);
//                if(!stopNo.trim().equals("300001")) {
//                    stop = new MediaStop();
//                    stop.setStopId(UUID.randomUUID() + "");
//                    stop.setStopNo(stopNo);
//                    stop.setStopName(stopName);
//                    if (direction.equals("东行") || direction.equals("西行") || direction.equals("南行")
//                            || direction.equals("北行")) {
//                        stop.setDirection(direction);
//                    } else {
//                        stop.setDirection("");
//                    }
//                    stop.setRemark(remark);
//                    stop.setCounty(county);
//                    stop.setZpjdSituation(zpjdSituation);
//                    stop.setLdSituation(ldSituation);
//                    stop.setHctssType(hctss);
//                    stop.setDeputyStopName(deputyStopName);
//                    stop.setRoadName(roadName);
//                    stop.setPassLines(passLines);
//                    stop.setPassLineCount(passLineCount);
//                    stop.setLineBoardCreator(lineBoardCreator);
//                    stop.setLineBoardMaterial(lineBoardMaterial);
//                    stop.setLineBoardSize(lineBoardSize);
//                    stop.setZgStyle(zgStyle);
//                    stop.setStatus("1");
//                    stop.setMakeTime(new Date());
//                    stop.setModifyTime(new Date());
//                    mediaStopMapper.insert(stop);
//                } else {
//                    stop = new MediaStop();
//                    stop.setStopId(UUID.randomUUID() + "");
//                    stop.setStopNo("TEMP_" + i);
//                    stop.setStopName(stopName);
//                    if (direction.equals("东行") || direction.equals("西行") || direction.equals("南行")
//                            || direction.equals("北行")) {
//                        stop.setDirection(direction);
//                    } else {
//                        stop.setDirection("");
//                    }
//                    stop.setRemark(remark);
//                    stop.setCounty(county);
//                    stop.setZpjdSituation(zpjdSituation);
//                    stop.setLdSituation(ldSituation);
//                    stop.setHctssType(hctss);
//                    stop.setDeputyStopName(deputyStopName);
//                    stop.setRoadName(roadName);
//                    stop.setPassLines(passLines);
//                    stop.setPassLineCount(passLineCount);
//                    stop.setLineBoardCreator(lineBoardCreator);
//                    stop.setLineBoardMaterial(lineBoardMaterial);
//                    stop.setLineBoardSize(lineBoardSize);
//                    stop.setZgStyle(zgStyle);
//                    stop.setStatus("1");
//                    stop.setMakeTime(new Date());
//                    stop.setModifyTime(new Date());
//                    mediaStopMapper.insert(stop);
//                }
//
//            }
//            -------------------------------------------------------------------------

//-------------------------------------------------------------------------
            if(stopNo.trim().equals("300001")) {
                stop = mediaStopMapper.selectByStopNo("TEMP_" + i);
            } else {
                stop = mediaStopMapper.selectByStopNo(stopNo);
            }
//-------------------------------------------------------------------------
//
//            // List<MediaStop> stopList =
//            // mediaStopMapper.selectByStopNameAndDirection(stopName,
//            // direction);
//            // if (stopList == null || stopList.size() == 0) {
//            // continue;
//            // }
//            // if(stopList.size() == 1) {
//            // stop = stopList.get(0);
//            // } else {
//            // if(StringUtils.isBlank(remark)) { // 描述为空
//            // stop = stopList.get(0);
//            // } else {
//            // List<MediaStop> tempList =
//            // mediaStopMapper.selectByStopNameAndDirectionAndRemark(stopName,
//            // direction, remark);
//            // if(tempList == null || tempList.size() == 0) {
//            // stop = stopList.get(0);
//            // } else {
//            // stop = tempList.get(0);
//            // }
//            // }
//            // }
//
//            // stop.setDeputyStopName(deputyStopName);
//            // stop.setZpjdSituation(zpjdSituation);
//            // stop.setStopNo(stopNo);
//            // if (ldSituation.equals("0")) {
//            // stop.setLdSituation("否");
//            // } else if (ldSituation.equals("1")) {
//            // stop.setLdSituation("是");
//            // } else {
//            // stop.setLdSituation(ldSituation);
//            // }
//
//            // stop.setRemark(remark);
//            -------------------------------------------------------------------------
            if (stop != null) {
                if (StringUtils.isNotBlank(customerName)) {
                    List<MediaResource> list = mediaResourceMapper.selectByStopId(stop.getStopId());
                    // int dabanCount = 0;
                    for (int j = 0; j < list.size(); j++) {

                        MediaResource mediaResource = list.get(j);
                        String sql = "SELECT c.CONTRACT_ID FROM `AD_CUSTOMER` a "
                                + "LEFT JOIN AD_CONTRACT c ON c.CUSTOMER_ID = a.CUSTOMER_ID "
                                + "where a.CUSTOMER_NAME = '" + customerName + "'";
                        String contractId = executeString(sql);
                        ContractResource contractResource = new ContractResource();
                        contractResource.setContractId(contractId);
                        contractResource.setId(intIncrementId("AD_CONTRACT_RESOURCE", "ID"));
                        if (customerName.equals("495")) {
                            contractResource.setStartDate(DateUtil.parse("2010-01-01"));
                            contractResource.setEndDate(DateUtil.parse("2021-12-31"));
                        } else if (customerName.equals("置换回安泰福")) {
                            contractResource.setStartDate(DateUtil.parse("2017-01-01"));
                            contractResource.setEndDate(DateUtil.parse("2026-12-31"));
                        } else if (customerName.equals("博瑞江南") || customerName.equals("博瑞江北")) {
                            contractResource.setStartDate(DateUtil.parse("2017-02-28"));
                            contractResource.setEndDate(DateUtil.parse("2020-02-27"));
                        } else if (customerName.equals("公交所属")) {
                            contractResource.setStartDate(DateUtil.parse("2000-01-01"));
                            contractResource.setEndDate(DateUtil.parse("2050-12-31"));
                        }
                        contractResource.setResourceId(mediaResource.getResourceId());
                        contractResourceMapper.insert(contractResource);
                    }
                }
            }
//            -------------------------------------------------------------------------

//            ---------------------------------资源导入----------------------------------------
//            if (StringUtils.isNotBlank(ledNum) && StringUtil.isNumeric(ledNum)) {
//                System.out.println(stop.getStopNo() + ":" + ledNum);
//                int tempCount = Integer.parseInt(ledNum);
//                for (int j = 0; j < tempCount; j++) {
//                    MediaResource mediaResource = new MediaResource();
//                    mediaResource.setResourceId(UUID.randomUUID().toString());
//                    mediaResource.setResourceCode("14-" + UUID.randomUUID().toString());
//                    mediaResource.setStopId(stop.getStopId());
//                    mediaResource.setStatus("1");
//                    mediaResource.setMediaType("14");
//                    mediaResource.setMakeTime(new Date());
//                    mediaResourceMapper.insert(mediaResource);
//                }
//
//            }
//            //
//            if (StringUtils.isNotBlank(bigBoardCount) && StringUtil.isNumeric(bigBoardCount)) {
//                int tempCount = Integer.parseInt(bigBoardCount);
//                for (int j = 0; j < tempCount; j++) {
//                    MediaResource mediaResource = new MediaResource();
//                    mediaResource.setResourceId(UUID.randomUUID().toString());
//                    mediaResource.setResourceCode("12-" + UUID.randomUUID().toString());
//                    mediaResource.setStopId(stop.getStopId());
//                    mediaResource.setStatus("1");
//                    mediaResource.setMediaType("12");
//                    mediaResource.setMakeTime(new Date());
//                    mediaResourceMapper.insert(mediaResource);
//                }
//            }
//
//            if (StringUtils.isNotBlank(smallBoardCount) && StringUtil.isNumeric(smallBoardCount)) {
//                int tempCount = Integer.parseInt(smallBoardCount);
//                for (int j = 0; j < tempCount; j++) {
//                    MediaResource mediaResource = new MediaResource();
//                    mediaResource.setResourceId(UUID.randomUUID().toString());
//                    mediaResource.setResourceCode("13-" + UUID.randomUUID().toString());
//                    mediaResource.setStopId(stop.getStopId());
//                    mediaResource.setStatus("1");
//                    mediaResource.setMediaType("13");
//                    mediaResource.setMakeTime(new Date());
//                    mediaResourceMapper.insert(mediaResource);
//                }
//            }
//            -------------------------------------------------------------------------
            // if (stop != null) {
            // if (StringUtils.isNotBlank(ledNum) &&
            // StringUtil.isNumeric(ledNum)) {
            // System.out.println(stop.getStopNo() + ":" + ledNum);
            // int tempCount = Integer.parseInt(ledNum);
            // for (int j = 0; j < tempCount; j++) {
            // MediaResource mediaResource = new MediaResource();
            // mediaResource.setResourceId(UUID.randomUUID().toString());
            // mediaResource.setResourceCode("14-" +
            // UUID.randomUUID().toString());
            // mediaResource.setStopId(stop.getStopId());
            // mediaResource.setStatus("1");
            // mediaResource.setMediaType("14");
            // mediaResource.setMakeTime(new Date());
            // mediaResourceMapper.insert(mediaResource);
            // }
            // if (StringUtils.isNotBlank(customerName) &&
            // customerName.equals("495")) {
            // List<MediaResource> list =
            // mediaResourceMapper.selectByStopId(stop.getStopId());
            // // int dabanCount = 0;
            // for (int j = 0; j < list.size(); j++) {
            //
            // MediaResource mediaResource = list.get(j);
            // String sql = "SELECT c.CONTRACT_ID FROM `AD_CUSTOMER` a "
            // + "LEFT JOIN AD_CONTRACT c ON c.CUSTOMER_ID = a.CUSTOMER_ID "
            // + "where a.CUSTOMER_NAME = '" + customerName + "'";
            // String contractId = executeString(sql);
            // ContractResource contractResource = new ContractResource();
            // contractResource.setContractId(contractId);
            // contractResource.setId(intIncrementId("AD_CONTRACT_RESOURCE",
            // "ID"));
            // if (customerName.equals("495")) {
            // contractResource.setStartDate(DateUtil.parse("2010-01-01"));
            // contractResource.setEndDate(DateUtil.parse("2021-12-31"));
            // } else if (customerName.equals("置换回安泰福")) {
            // contractResource.setStartDate(DateUtil.parse("2017-01-01"));
            // contractResource.setEndDate(DateUtil.parse("2026-12-31"));
            // } else if (customerName.equals("博瑞江南") ||
            // customerName.equals("博瑞江北")) {
            // contractResource.setStartDate(DateUtil.parse("2017-02-28"));
            // contractResource.setEndDate(DateUtil.parse("2020-02-27"));
            // } else if (customerName.equals("公交所属")) {
            // contractResource.setStartDate(DateUtil.parse("2000-01-01"));
            // contractResource.setEndDate(DateUtil.parse("2050-12-31"));
            // }
            // contractResource.setResourceId(mediaResource.getResourceId());
            // contractResourceMapper.insert(contractResource);
            // // if (mediaResource.getMediaType().equals("12")) {
            // // //候车亭大板
            //
            // // hctCount++;
            // // dabanCount++;
            // // }
            // }
            // // System.out.println("编号：" + stopNo + "----- 大板数："
            // // +dabanCount);
            // }
            //
            // }
            //
            // // stop.setHctssType(hctss);
            // // mediaStopMapper.updateByPrimaryKey(stop);
            // } else {
            // System.out.println(stopNo + "---" + customerName);
            // }
            //
            // if (StringUtils.isNotBlank(bigBoardCount) &&
            // StringUtil.isNumeric(bigBoardCount)) {
            // int tempCount = Integer.parseInt(bigBoardCount);
            // for (int j = 0; j < tempCount; j++) {
            // MediaResource mediaResource = new MediaResource();
            // mediaResource.setResourceId(UUID.randomUUID().toString());
            // mediaResource.setResourceCode("12-" +
            // UUID.randomUUID().toString());
            // mediaResource.setStopId(stop.getStopId());
            // mediaResource.setStatus("1");
            // mediaResource.setMediaType("12");
            // mediaResource.setMakeTime(new Date());
            // mediaResourceMapper.insert(mediaResource);
            // }
            // }
            //
            // if (StringUtils.isNotBlank(smallBoardCount) &&
            // StringUtil.isNumeric(smallBoardCount)) {
            // int tempCount = Integer.parseInt(smallBoardCount);
            // for (int j = 0; j < tempCount; j++) {
            // MediaResource mediaResource = new MediaResource();
            // mediaResource.setResourceId(UUID.randomUUID().toString());
            // mediaResource.setResourceCode("13-" +
            // UUID.randomUUID().toString());
            // mediaResource.setStopId(stop.getStopId());
            // mediaResource.setStatus("1");
            // mediaResource.setMediaType("13");
            // mediaResource.setMakeTime(new Date());
            // mediaResourceMapper.insert(mediaResource);
            // }
            // }

            // String vehicleNo = dataList.get(i)[0];
            // String plateNo = dataList.get(i)[7];
            //
            // MediaBus mediaBus = mediaBusMapper.selectByVehicleNo(vehicleNo);
            // if (mediaBus == null) {
            // System.out.println(plateNo + " : " + vehicleNo);
            // } else {
            // String busType = dataList.get(i)[1];
            // BusType type = busTypeMapper.selectByTypeCode(busType);
            // if (type == null || StringUtils.isBlank(type.getTypeId())) {
            // type = new BusType();
            // type.setTypeId(intIncrementId("MEDIA_BUS_TYPE", "TYPE_ID"));
            // type.setStatus("1");
            // type.setTypeCode(busType);
            // type.setTypeName(busType);
            // busTypeMapper.insert(type);
            // }
            // String typeId = type.getTypeId();
            // mediaBus.setBusType(typeId);
            // String busFloor = dataList.get(i)[2];
            // if ("双巴".equals(busFloor)) {
            // mediaBus.setBusFloor(Constant.BUS_FLOOR_DOUBLE);
            // } else {
            // mediaBus.setBusFloor("");
            // }
            //
            // String airConditioned = dataList.get(i)[3];
            // if ("空调".equals(airConditioned)) {
            // mediaBus.setAirConditioned("1");
            // } else if ("非空调".equals(airConditioned)) {
            // mediaBus.setAirConditioned("0");
            // } else {
            // mediaBus.setAirConditioned("");
            // }
            //
            // String busEnergy = dataList.get(i)[4];
            // if ("纯电动".equals(busEnergy)) {
            // mediaBus.setBusEnergy("1");
            // } else if ("新能源".equals(busEnergy)) {
            // mediaBus.setBusEnergy("2");
            // } else if ("电车新能源".equals(busEnergy)) {
            // mediaBus.setBusEnergy("3");
            // } else {
            // mediaBus.setBusEnergy("");
            // }
            //
            // String businessType = dataList.get(i)[5];
            // if ("微循环".equals(businessType)) {
            // mediaBus.setBusinessType("1");
            // } else if ("微循环-8米".equals(businessType)) {
            // mediaBus.setBusinessType("2");
            // } else {
            // mediaBus.setBusinessType("");
            // }
            //
            // String onlineYear = dataList.get(i)[6];
            // if (onlineYear.length() > 4) {
            // Date temp = DateUtil.formatExcelDate(onlineYear,
            // "EEE MMM dd HH:mm:ss z yyyy", "yyyy-MM-dd");
            // Calendar c = Calendar.getInstance();
            // c.setTime(temp);
            // onlineYear = String.valueOf(c.get(Calendar.YEAR));
            // }
            // mediaBus.setOnlineDate(onlineYear);
            //
            // mediaBusMapper.updateBusInfo(mediaBus);
            //
            // MediaResource mediaResource = new MediaResource();
            // mediaResource.setResourceId(UUID.randomUUID().toString());
            // mediaResource.setResourceCode("8-" + System.currentTimeMillis() +
            // i);
            // mediaResource.setBusId(mediaBus.getBusId());
            // mediaResource.setStatus("1");
            // mediaResource.setMediaType("8");
            // mediaResource.setMakeTime(new Date());
            //
            // mediaResourceMapper.insert(mediaResource);
            //
            // }

            // mediaStopMapper.insert(stop);
        }
        // System.out.println("总大半数：" + hctCount);
        System.out.println("导入成功!");

    }

    public JSONObject synchronousBus(ReqBean reqBean) throws Exception {

        String doGetStr = HttpUtil.doGet(
                TOKEN_ASSESSURL.replace("corpId", TOKEN_CORPID).replace("corpSecret", TOKEN_SECRET), null);
        if (StringUtils.isBlank(doGetStr)) {
            throw new BusinessException("doGet 获取ASSESS_TOKEN失败!");
        }

        JSONObject returnJson = new JSONObject();
        System.out.println(JSONObject.fromObject(doGetStr));

        JSONObject assessToken = JSONObject.fromObject(doGetStr);
        if (assessToken != null && !"{}".equals(assessToken)) {
            if (assessToken.getString("errcode").equals("40004")) {
                throw new BusinessException(doGetStr);
            }
            String token = assessToken.getString("access_token");

            Map<String, String> params = new HashMap<String, String>();

            params.put("access_token", token);
            params.put("key", "getlist");

            String doPostStr = HttpUtil.postWithUrlEncodedFormEntity(TOKEN_BUSURL, params);
            if (StringUtils.isBlank(doPostStr)) {
                throw new BusinessException("doPost 获取车辆数据失败!");
            }
            int count = 0;
            String vehicleNoStr = "";
            returnJson = JSONObject.fromObject(doPostStr);
            JSONArray array = returnJson.getJSONArray("veh_list");
            System.out.println("total : " + array.size());
            for (int i = 0; i < array.size(); i++) {
                JSONObject json = array.getJSONObject(i);
                String vehicleNo = json.getString("code");
                MediaBus mediaBus = mediaBusMapper.selectByVehicleNo(vehicleNo);
                if (mediaBus == null) {
                    System.out.println("自编号为" + vehicleNo + "未在数据库中找到!");
                } else {
                    vehicleNoStr = vehicleNoStr + "\"" + vehicleNo + "\",";
                }
                if (Boolean.parseBoolean(json.getString("is_producing"))) {
                    count++;
                }
            }
            System.out.println(vehicleNoStr);
            returnJson.put("runTotal", count);
        }

        return returnJson;

    }

    public JSONArray runDynamicQuery(ReqBean reqBean) {

        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");

        String notRunDay = reqBean.getInfo("notRunDay");

        String queryType = reqBean.getInfo("queryType");

        if (StringUtil.isEmpty(startDate) || StringUtil.isEmpty(endDate) || StringUtil.isEmpty(notRunDay)
                || StringUtil.isEmpty(queryType)) {
            throw new BusinessException("缺少参数!");
        }

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        int dayCount = DateUtil.getBetweenDays(DateUtil.parse(endDate), DateUtil.parse(startDate)) + 1;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        Map<String, JSONObject> map = new HashMap<String, JSONObject>();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = " select ("
                    + dayCount
                    + " - t.totalCount) notRunDay,t.*,b.Veh_RegCode plateNo,b.Veh_ID busId,b.Company_Name companyName,b.Dept_Name deptName,b.Veh_LineName lineName FROM "
                    + "(SELECT COUNT (1) totalCount,r.vehicleNo FROM "
                    + "(SELECT a.O_CLMC vehicleNo,a.O_RQ runDate FROM dbo.Tbl_OP_Rundynamic_Main a "
                    + "WHERE a.O_YYTC > 0 and a.O_RQ >= '" + startDate + "' AND a.O_RQ <= '" + endDate
                    + "' GROUP BY a.O_CLMC, a.O_RQ ) r " + "GROUP BY r.vehicleNo HAVING (" + dayCount
                    + " - COUNT (1)) >= " + "'" + notRunDay + "') t "
                    + "LEFT JOIN dbo.Tbl_Core_Vehicle AS b ON t.vehicleNo= b.Veh_Code "
                    + "where b.Veh_IsProducing = 1 AND b.Veh_IsDelete = 0 AND b.Veh_State < 99";

            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                // System.out.println(rs.getString("vehicleNo"));
                data.put("notRunDay", rs.getString("notRunDay"));
                data.put("totalCount", rs.getString("totalCount"));
                data.put("vehicleNo", rs.getString("vehicleNo"));
                data.put("companyName", rs.getString("companyName"));
                data.put("deptName", rs.getString("deptName"));
                data.put("lineName", rs.getString("lineName"));
                data.put("plateNo", rs.getString("plateNo"));
                data.put("busId", rs.getString("busId"));
                map.put(rs.getString("vehicleNo"), data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if ("1".equals(queryType)) { // 全部
            for (Map.Entry<String, JSONObject> entry : map.entrySet()) {
                returnArr.add(entry.getValue());
            }
        } else if ("2".equals(queryType)) { // 已做广告
            String sql = "SELECT d.RESOURCE_ID,r.BUS_ID,b.VEHICLE_NO FROM `AD_PUBLISH_BILL` a "
                    + "LEFT JOIN AD_PUBLISH_BILL_DETAIL d ON a.BILL_ID = d.BILL_ID "
                    + "LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = d.RESOURCE_ID "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = r.BUS_ID " + "where a.PUBLISH_TIME <= '" + startDate
                    + "' AND a.PUBLISH_OFF_TIME >= '" + endDate + "' GROUP BY b.VEHICLE_NO";
            JSONArray busList = query(sql);
            if (busList != null && busList.size() > 0) {
                for (int i = 0; i < busList.size(); i++) {
                    JSONObject busData = busList.getJSONObject(i);
                    JSONObject searchData = map.get(busData.getString("vehicleNo"));
                    if (searchData != null) {
                        returnArr.add(searchData);
                    }
                }
            }
        } else if ("3".equals(queryType)) { // 未做广告
            String sql = "SELECT d.RESOURCE_ID,r.BUS_ID,b.VEHICLE_NO FROM `AD_PUBLISH_BILL` a "
                    + "LEFT JOIN AD_PUBLISH_BILL_DETAIL d ON a.BILL_ID = d.BILL_ID "
                    + "LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = d.RESOURCE_ID "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = r.BUS_ID " + "where a.PUBLISH_TIME <= '" + startDate
                    + "' AND a.PUBLISH_OFF_TIME >= '" + endDate + "' GROUP BY b.VEHICLE_NO";
            JSONArray busList = query(sql);
            if (busList != null && busList.size() > 0) {
                for (int i = 0; i < busList.size(); i++) {
                    JSONObject busData = busList.getJSONObject(i);
                    JSONObject searchData = map.get(busData.getString("vehicleNo"));
                    if (searchData != null) {
                        map.remove(busData.getString("vehicleNo"));
                    }
                }
            }

            for (Map.Entry<String, JSONObject> entry : map.entrySet()) {
                returnArr.add(entry.getValue());
            }

        }
        return returnArr;
    }

    public JSONArray runDynamicPublish(ReqBean reqBean) {
        String dataList = reqBean.getInfo("showDataList");
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");
        if (StringUtils.isBlank(dataList) || "[]".equals(dataList) || StringUtils.isBlank(startDate)
                || StringUtils.isBlank(endDate)) {
            throw new BusinessException("请填写开始时间和结束时间!");
        }

        JSONArray array = JSONArray.fromObject(dataList);
        if (array != null && array.size() > 0) {
            for (int i = 0; i < array.size(); i++) {
                JSONObject json = array.getJSONObject(i);
                String busId = json.getString("busId");
                String sql = "SELECT a.BILL_ID,c.CUSTOMER_NAME,a.CONTENT FROM `AD_PUBLISH_BILL` a "
                        + "LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = a.CUSTOMER_ID "
                        + "LEFT JOIN AD_PUBLISH_BILL_DETAIL d ON d.BILL_ID = a.BILL_ID "
                        + "LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = d.RESOURCE_ID " + "where a.PUBLISH_TIME <= '"
                        + startDate + "' AND a.PUBLISH_OFF_TIME >= '" + endDate + "' " + "AND r.BUS_ID = '" + busId
                        + "' GROUP BY a.BILL_ID";
                JSONArray publishContents = query(sql);
                String customerAndContent = "";
                for (int j = 0; j < publishContents.size(); j++) {
                    JSONObject o = publishContents.getJSONObject(j);
                    customerAndContent = customerAndContent + o.getString("customerName") + "-"
                            + o.getString("content") + ",";
                }
                if (StringUtils.isNotBlank(customerAndContent)) {
                    json.put("customerContent", customerAndContent.substring(0, customerAndContent.length() - 2));
                }
            }
        }

        return array;
    }

    public JSONArray runDynamicDetail(ReqBean reqBean) {
        String vehicleNo = reqBean.getInfo("vehicleNo");
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");
        if (StringUtils.isBlank(vehicleNo) || StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate)) {
            throw new BusinessException("缺少参数!");
        }

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = "SELECT b.Veh_LineName AS fromLineName, b.Veh_RegCode plateNo,a.O_XLMC AS runLineName,"
                    + "a.O_RQ runDate FROM dbo.Tbl_OP_Rundynamic_Main AS a "
                    + "LEFT JOIN dbo.Tbl_Core_Vehicle AS b ON a.O_CLMC = b.Veh_Code "
                    + "WHERE O_YYTC > 0 AND a.O_RQ >= '" + startDate + "' AND a.O_RQ <= '" + endDate + "' "
                    + "AND b.Veh_IsProducing = 1 AND b.Veh_IsDelete = 0 AND b.Veh_State < 99 AND a.O_CLMC = '"
                    + vehicleNo + "'";

            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                data.put("fromLineName", rs.getString("fromLineName"));
                data.put("plateNo", rs.getString("plateNo"));
                data.put("runLineName", rs.getString("runLineName"));
                data.put("runDate", rs.getString("runDate"));
                returnArr.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return returnArr;
    }

    /**
     * 公司内车辆异动
     * 
     * @param reqBean
     * @return
     */
    public JSONArray companyInnerTrans(ReqBean reqBean) {
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");

        String vehicleNo = reqBean.getInfo("vehicleNo");

        String plateNo = reqBean.getInfo("plateNo");

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = "SELECT a.CompanyIn companyIn,a.CompanyOut companyOut,a.LineIn lineIn,a.LineOut lineOut,"
                    + "a.VehTransfer_ID vehTransferId,a.VehTransfer_Reason reason,a.VehTransfer_Time vehTransferTime,"
                    + "v.Veh_RegCode plateNo,v.Veh_Code vehicleNo from Tbl_TE_BatchVehicleTransfer a "
                    + "LEFT JOIN Tbl_TE_BatchVehicleTransferDetail d ON d.VehTransfer_ID = a.VehTransfer_ID "
                    + "LEFT JOIN Tbl_Core_Vehicle v ON v.Veh_ID = d.Vehicle_ID " + "where  a.VehTransfer_Status = '4' ";
            if (StringUtils.isNotBlank(startDate)) {
                sql = sql + "and a.VehTransfer_Time >= '" + startDate + "' ";
            }

            if (StringUtils.isNotBlank(endDate)) {
                sql = sql + "and a.VehTransfer_Time <= '" + endDate + "' ";
            }

            if (StringUtils.isNotBlank(plateNo)) {
                sql = sql + "and v.Veh_RegCode like '%" + plateNo + "%' ";
            }

            if (StringUtils.isNotBlank(vehicleNo)) {
                sql = sql + "and v.Veh_Code like '%" + vehicleNo + "%' ";
            }

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs.getString("plateNo"));
                data.put("vehicleNo", rs.getString("vehicleNo"));
                data.put("lineIn", rs.getString("lineIn"));
                data.put("companyIn", rs.getString("companyIn"));
                data.put("companyOut", rs.getString("companyOut"));
                data.put("lineOut", rs.getString("lineOut"));
                data.put("vehTransferId", rs.getString("vehTransferId"));
                data.put("reason", rs.getString("reason"));
                data.put("vehTransferTime", rs.getString("vehTransferTime"));
                returnArr.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return returnArr;
    }

    /**
     * 公司间异动
     * 
     * @param reqBean
     * @return
     */
    public JSONArray companyTrans(ReqBean reqBean) {
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");

        String companyIn = reqBean.getInfo("companyIn");

        String companyOut = reqBean.getInfo("companyOut");

        String vehicleNo = reqBean.getInfo("vehicleNo");

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.CompanyIn companyIn,a.VehTransfer_Plan_Datetime planTime,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "where a.VehTransfer_Type = '99CBCBF8-0F68-4190-BA74-724F88EBEA18' AND a.VehTransfer_Status = '4' ";
            if (StringUtils.isNotBlank(startDate)) {
                sql = sql + "and a.VehTransfer_Time >= '" + startDate + "' ";
            }

            if (StringUtils.isNotBlank(endDate)) {
                sql = sql + "and a.VehTransfer_Time <= '" + endDate + "' ";
            }

            if (StringUtils.isNotBlank(companyIn)) {
                sql = sql + "and a.CompanyIn like '%" + companyIn + "%' ";
            }

            if (StringUtils.isNotBlank(companyOut)) {
                sql = sql + "and a.CompanyOut like '%" + companyOut + "%' ";
            }

            if (StringUtils.isNotBlank(companyOut)) {
                sql = sql + "and a.CompanyOut like '%" + companyOut + "%' ";
            }

            if (StringUtils.isNotBlank(vehicleNo)) {
                sql = sql + "and b.Veh_Code like '%" + vehicleNo + "%' ";
            }

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs.getString("plateNo"));
                data.put("vehicleNo", rs.getString("vehicleNo"));
                data.put("companyOut", rs.getString("companyOut"));
                data.put("companyIn", rs.getString("companyIn"));
                data.put("reason", rs.getString("reason"));
                data.put("vehTransferTime", rs.getString("vehTransferTime"));
                returnArr.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return returnArr;
    }

    /**
     * 使用性质异动
     * 
     * @param reqBean
     * @return
     */
    public JSONArray vehicleUsing(ReqBean reqBean) {
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");

        String plateNo = reqBean.getInfo("plateNo");

        String vehicleNo = reqBean.getInfo("vehicleNo");

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.VehTransfer_Before_BusKind beforeBusKind,a.VehTransfer_After_BusKind afterBusKind,d1.DictItem_DisplayName beforeBusSort,"
                    + "d2.DictItem_DisplayName afterBusSort,a.CompanyIn companyIn,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "LEFT JOIN Tbl_TE_DictItem d1 ON d1.DictItem_ID = a.VehTransfer_Before_VehMod_Sort "
                    + "LEFT JOIN Tbl_TE_DictItem d2 ON d2.DictItem_ID = a.VehTransfer_After_VehMod_Sort "
                    + "where a.VehTransfer_Type = '3EE90170-4143-4A25-B0F0-AC2F7693DC3D' AND a.VehTransfer_Status = '4' ";
            if (StringUtils.isNotBlank(startDate)) {
                sql = sql + "and a.VehTransfer_Time >= '" + startDate + "' ";
            }

            if (StringUtils.isNotBlank(endDate)) {
                sql = sql + "and a.VehTransfer_Time <= '" + endDate + "' ";
            }

            if (StringUtils.isNotBlank(plateNo)) {
                sql = sql + "and b.Veh_RegCode like '%" + plateNo + "%' ";
            }

            if (StringUtils.isNotBlank(vehicleNo)) {
                sql = sql + "and b.Veh_Code like '%" + vehicleNo + "%' ";
            }

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs.getString("plateNo"));
                data.put("vehicleNo", rs.getString("vehicleNo"));
                data.put("beforeBusKind", rs.getString("beforeBusKind"));
                data.put("afterBusKind", rs.getString("afterBusKind"));
                data.put("beforeBusSort", rs.getString("beforeBusSort"));
                data.put("afterBusSort", rs.getString("afterBusSort"));
                data.put("companyIn", rs.getString("companyIn"));
                data.put("companyOut", rs.getString("companyOut"));
                data.put("reason", rs.getString("reason"));
                data.put("vehTransferTime", rs.getString("vehTransferTime"));
                returnArr.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return returnArr;

    }

    /**
     * 资源日查询
     * 
     * @param reqBean
     * @return
     */
    public JSONArray resourceDayQuery(ReqBean reqBean) {

        String startDate = reqBean.getInfo("startDate");

        String endDate = reqBean.getInfo("endDate");

        String lineNo = reqBean.getInfo("lineNo");

        if (StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate) || StringUtils.isBlank(lineNo)) {
            throw new BusinessException("非法请求!");
        }

        String plateNo = reqBean.getInfo("plateNo");

        String vehicleNo = reqBean.getInfo("vehicleNo");

        String companyName = reqBean.getInfo("companyName");

        String driverName = DRIVER_NAME;

        String dbURL = DB_URL;

        String userName = USERNAME;

        String password = PASSWORD;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        JSONArray returnArr = new JSONArray();
        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(dbURL, userName, password);

            String sql = "select t.*,b.Veh_RegCode plateNo,b.Company_Name companyName FROM "
                    + "(SELECT a.O_XLMC searchLineNo,a.O_CLMC vehicleNo,a.O_ZKGLEX,a.O_RQ runDay "
                    + "FROM dbo.Tbl_OP_Rundynamic_Main AS a WHERE O_ZKGLEX > 0 GROUP BY a.O_XLMC,a.O_CLMC,a.O_ZKGLEX,a.O_RQ ) t "
                    + "LEFT JOIN dbo.Tbl_Core_Vehicle AS b ON t.vehicleNo = b.Veh_Code "
                    + "WHERE b.Veh_IsProducing = 1 AND b.Veh_IsDelete = 0 AND b.Veh_State < 99 " + "AND t.runDay >= '"
                    + startDate + "' AND t.runDay <= '" + endDate + "' AND t.searchLineNo = '" + lineNo + "' ";

            if (StringUtils.isNotBlank(plateNo)) {
                sql = sql + "and b.Veh_RegCode LIKE '%" + plateNo + "%' ";
            }

            if (StringUtils.isNotBlank(vehicleNo)) {
                sql = sql + "and a.O_CLMC LIKE '%" + vehicleNo + "%' ";
            }

            if (StringUtils.isNotBlank(companyName)) {
                sql = sql + "and b.Company_Name LIKE '%" + companyName + "%' ";
            }

            sql = sql + " ORDER BY t.runDay DESC";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs.getString("plateNo"));
                data.put("vehicleNo", rs.getString("vehicleNo"));
                data.put("searchLineNo", rs.getString("searchLineNo"));
                data.put("companyName", rs.getString("companyName"));
                data.put("runDay", rs.getString("runDay"));
                returnArr.add(data);
            }
            rs.close();
            pstmt.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return returnArr;
    }

    public JSONObject transQuery(ReqBean reqBean) {
        String busId = reqBean.getInfo("busId");
        if (StringUtils.isBlank(busId)) {
            throw new BusinessException("缺少参数!");
        }

        Connection conn = null;
        PreparedStatement pstmt1 = null;
        ResultSet rs1 = null;

        PreparedStatement pstmt2 = null;
        ResultSet rs2 = null;

        PreparedStatement pstmt3 = null;
        ResultSet rs3 = null;

        JSONObject returnJson = new JSONObject();
        JSONArray companyInnerTransArr = new JSONArray();

        JSONArray companyTransArr = new JSONArray();

        JSONArray vehicleUsingArr = new JSONArray();

        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);

            String sql = "SELECT a.CompanyIn companyIn,a.CompanyOut companyOut,a.LineIn lineIn,a.LineOut lineOut,"
                    + "a.VehTransfer_ID vehTransferId,a.VehTransfer_Reason reason,a.VehTransfer_Time vehTransferTime,"
                    + "v.Veh_RegCode plateNo,v.Veh_Code vehicleNo from Tbl_TE_BatchVehicleTransfer a "
                    + "LEFT JOIN Tbl_TE_BatchVehicleTransferDetail d ON d.VehTransfer_ID = a.VehTransfer_ID "
                    + "LEFT JOIN Tbl_Core_Vehicle v ON v.Veh_ID = d.Vehicle_ID " + "where  a.VehTransfer_Status = '4' "
                    + "and v.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt1 = conn.prepareStatement(sql);
            rs1 = pstmt1.executeQuery();
            while (rs1.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs1.getString("plateNo"));
                data.put("vehicleNo", rs1.getString("vehicleNo"));
                data.put("lineIn", rs1.getString("lineIn"));
                data.put("companyIn", rs1.getString("companyIn"));
                data.put("companyOut", rs1.getString("companyOut"));
                data.put("lineOut", rs1.getString("lineOut"));
                data.put("vehTransferId", rs1.getString("vehTransferId"));
                data.put("reason", rs1.getString("reason"));
                data.put("vehTransferTime", rs1.getString("vehTransferTime"));
                data.put("type", Constant.BUS_TRANS_TYPE_INNER);
                companyInnerTransArr.add(data);
            }
            rs1.close();
            pstmt1.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {
            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.CompanyIn companyIn,a.VehTransfer_Plan_Datetime planTime,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "where a.VehTransfer_Type = '99CBCBF8-0F68-4190-BA74-724F88EBEA18' AND a.VehTransfer_Status = '4' "
                    + "and b.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt2 = conn.prepareStatement(sql);
            rs2 = pstmt2.executeQuery();
            while (rs2.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs2.getString("plateNo"));
                data.put("vehicleNo", rs2.getString("vehicleNo"));
                data.put("companyOut", rs2.getString("companyOut"));
                data.put("companyIn", rs2.getString("companyIn"));
                data.put("reason", rs2.getString("reason"));
                data.put("vehTransferTime", rs2.getString("vehTransferTime"));
                data.put("type", Constant.BUS_TRANS_TYPE_COMPANY);
                companyTransArr.add(data);
            }
            rs2.close();
            pstmt2.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt2.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {

            String sql = "SELECT b.Veh_RegCode plateNo,b.Veh_Code vehicleNo,a.CompanyOut companyOut,a.VehTransfer_Reason reason,"
                    + "a.VehTransfer_Before_BusKind beforeBusKind,a.VehTransfer_After_BusKind afterBusKind,d1.DictItem_DisplayName beforeBusSort,"
                    + "d2.DictItem_DisplayName afterBusSort,a.CompanyIn companyIn,a.VehTransfer_Time vehTransferTime "
                    + "FROM Tbl_TE_VehicleTransfer AS a "
                    + "LEFT JOIN Tbl_Core_Vehicle AS b ON b.Veh_ID = a.Vehicle_Id "
                    + "LEFT JOIN Tbl_TE_DictItem d1 ON d1.DictItem_ID = a.VehTransfer_Before_VehMod_Sort "
                    + "LEFT JOIN Tbl_TE_DictItem d2 ON d2.DictItem_ID = a.VehTransfer_After_VehMod_Sort "
                    + "where a.VehTransfer_Type = '3EE90170-4143-4A25-B0F0-AC2F7693DC3D' AND a.VehTransfer_Status = '4' "
                    + "and b.Veh_ID = '" + busId + "'";

            sql = sql + " order by a.VehTransfer_Time desc";
            pstmt3 = conn.prepareStatement(sql);
            rs3 = pstmt3.executeQuery();
            while (rs3.next()) {
                JSONObject data = new JSONObject();
                data.put("plateNo", rs3.getString("plateNo"));
                data.put("vehicleNo", rs3.getString("vehicleNo"));
                data.put("beforeBusKind", rs3.getString("beforeBusKind"));
                data.put("afterBusKind", rs3.getString("afterBusKind"));
                data.put("beforeBusSort", rs3.getString("beforeBusSort"));
                data.put("afterBusSort", rs3.getString("afterBusSort"));
                data.put("companyIn", rs3.getString("companyIn"));
                data.put("companyOut", rs3.getString("companyOut"));
                data.put("reason", rs3.getString("reason"));
                data.put("vehTransferTime", rs3.getString("vehTransferTime"));
                data.put("type", Constant.BUS_TRANS_TYPE_USING);
                vehicleUsingArr.add(data);
            }
            rs3.close();
            pstmt3.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                rs3.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt3.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        returnJson.put("companyInnerTransList", companyInnerTransArr);
        returnJson.put("companyTransList", companyTransArr);
        returnJson.put("vehicleUsingList", vehicleUsingArr);

        return returnJson;
    }
}